﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;

namespace DAL
{
    public class SqlServerConnection
    {
        protected string connectionString = null;
        private SqlConnection connection = null;
        private SqlCommand command = null;

        /// 
        /// <param name="connectionString"></param>
        public SqlServerConnection(string connectionString)
        {
            this.connectionString = connectionString;
        }

        public SqlConnection GetConnection()
        {
            try
            {
                if (this.connection == null)
                {
                    this.connection = new SqlConnection(connectionString);
                }
                this.connection.Open();
            }
            catch (System.Exception)
            {
                throw new Exception("Không thể kết nối Database");
            }
            return this.connection;
        }

        public SqlCommand GetCommand()
        {
            try
            {
                if (this.command == null)
                {
                    this.command = this.GetConnection().CreateCommand();
                }
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            return this.command;
        }

        /// 
        /// <param name="SqlQuery"></param>
        public SqlDataReader ExecuteQuery(string sqlQuery)
        {
            SqlDataReader reader = null;
            try
            {
                this.GetCommand().CommandText = sqlQuery;
                reader = this.command.ExecuteReader();        
                return reader;
            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Errors[0].Message);
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
        }

        /// 
        /// <param name="SqlQuery"></param>
        public void ExecuteNonQuery(string sqlQuery)
        {
            try
            {
                this.GetCommand().CommandText = sqlQuery;
                this.command.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw new Exception(ex.Errors[0].Message);
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            finally
            {
                this.Disconnect();
            }
        }

        public void Disconnect()
        {
            try
            {
                if (this.connection != null)
                {
                    this.connection.Close();
                    this.connection.Dispose();
                }
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
        }

    }//end SqlServerConnection
}